﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Core.EntityClient;
using System.Data.Entity.Core.Objects;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using EFBasic.Entities;
using EFBasic.Map;

namespace EFBasic
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var ctx = new SchoolContext())
            {
                //Student stu = new Student { StudentName = "New student" };

                //ctx.Students.Add(stu);
                //ctx.SaveChanges();

                //var stud = new Student {Standard = new Standard {Description = "New standard"}, StudentName = "New stu"};
                //ctx.Students.Add(stud);
                //ctx.SaveChanges();

                var stuEntity = ctx.Students.FirstOrDefault<Student>();
                var entityType = ObjectContext.GetObjectType(stuEntity.GetType()); // get actual entity type from dynamic proxy

                // 1. Linq to Entities 
                LinqToEntities(ctx);
            }

            // 2. Entity SQL
            //EntitySQL();
            
            // 3. Native SQL
            //NativeSQL();

            // DBEntityentry class
            DBEntityentry();

            // Change tracking
            //TrackingChange();

            // connected senario
            //ConnectedSenario();

            // disconnected senario
            DisconnectedSenario(); 
            /* DbSet.Add() - auto set state = Added
             * Update must manually set state = Modified and child obj as well, recommend use connected senario
             * DbSet.Remove() - recommend use remove by obj for concurrency checking
             */

            //ConcurrencyCheck();
            //TestLocalData();
            //EagerLoading();
            //LazyLoading();
            //ExplicitLoading();

            TPH();
            TPT();

            Console.WriteLine("Run successfully");
            Console.ReadLine();
        }

        private static void LinqToEntities(SchoolContext ctx)
        {
            var lMethod = ctx.Students.FirstOrDefault(x => x.StudentName == "Bill");

            var lQuery = from st in ctx.Students
                where st.StudentName == "Bill"
                select st;

            var nestedQuery = from s in ctx.Students
                from c in s.Courses
                where s.StandardId == 1
                select new {s.StudentName, c};
        }

        private static void EntitySQL()
        {
            // 1. Querying with Object Services and Entity SQL
            using (var ctx = new SchoolContext())
            {
                var query = "SELECT VALUE st FROM Students " +
                            "AS st WHERE st.StudentName == 'New student'";

                var objctx = (ctx as IObjectContextAdapter).ObjectContext;

                ObjectQuery<Student> student = objctx.CreateQuery<Student>(query);
                var newstud = student.FirstOrDefault();
            }

            // 2. Have to use with connection string System.Data.EntityClient
            using (var con = new EntityConnection("name=SchoolDBConnectionString"))
            {
                con.Open();
                EntityCommand cmd = con.CreateCommand();
                cmd.CommandText = "SELECT VALUE st FROM Students as st where st.StudentName='Bill'";
                Dictionary<int, string> dict = new Dictionary<int, string>();
                using (EntityDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection))
                {
                    while (rdr.Read())
                    {
                        int a = rdr.GetInt32(0);
                        var b = rdr.GetString(1);
                        dict.Add(a, b);
                    }
                }
            }
        }

        private static void NativeSQL()
        {
            using (var ctx = new SchoolContext())
            {
                var student = ctx.Students.SqlQuery(@"SELECT * 
                                                        FROM Students as st 
                                                        where st.StudentName like 'New stu'");
                    // return Student type

                //Get student name of string type
                var studentNameString =
                    ctx.Database.SqlQuery<string>("Select studentname from Student where studentid=1")
                        .FirstOrDefault<string>();

                //Update command
                int noOfRowUpdated =
                    ctx.Database.ExecuteSqlCommand(
                        @"Update student 
                            set studentname ='changed student by command' 
                            where studentid=1");
                //Insert command
                int noOfRowInserted =
                    ctx.Database.ExecuteSqlCommand(@"insert into student(studentname) 
                                                                values('New Student')");
                //Delete command
                int noOfRowDeleted = ctx.Database.ExecuteSqlCommand("delete from student where studentid=1");
            }
        }

        private static void DBEntityentry()
        {
            using (var ctx = new SchoolContext())
            {
                //get student whose StudentId is 1
                var student = ctx.Students.Find(1);

                //edit student name
                student.StudentName = "Edited name";

                //get DbEntityEntry object for student entity object
                var entry = ctx.Entry(student);

                //get entity information e.g. full name
                Console.WriteLine("Entity Name: {0}", entry.Entity.GetType().FullName);

                //get current EntityState
                Console.WriteLine("Entity State: {0}", entry.State);

                Console.WriteLine("********Property Values********");

                foreach (var propertyName in entry.CurrentValues.PropertyNames)
                {
                    Console.WriteLine("Property Name: {0}", propertyName);

                    //get original value
                    var orgVal = entry.OriginalValues[propertyName];
                    Console.WriteLine("     Original Value: {0}", orgVal);

                    //get current values
                    var curVal = entry.CurrentValues[propertyName];
                    Console.WriteLine("     Current Value: {0}", curVal);
                }
            }
        }

        private static void TrackingChange()
        {
            Console.WriteLine("-----------------------");
            using (var ctx = new SchoolContext())
            {
                Console.WriteLine("Find student");
                var stu1 = ctx.Students.Find(1);
                Console.WriteLine("Context tracking changes of {0} entity", ctx.ChangeTracker.Entries().Count());
                DisplayTrackedEntities(ctx.ChangeTracker);

                Console.WriteLine("Find studend");
                var standard = ctx.Students.Find(2);
                Console.WriteLine("Context tracking changes of {0} entities.", ctx.ChangeTracker.Entries().Count());
                Console.WriteLine("");
                Console.WriteLine("Editing Student");
                standard.StudentName = "Edited name";
                DisplayTrackedEntities(ctx.ChangeTracker);

                var newStand = new Standard {StandardName = "standard name"};
                Console.WriteLine("Add new standard");
                ctx.Standards.Add(newStand);
                Console.WriteLine("");
                Console.WriteLine("Context tracking changes of {0} entities.", ctx.ChangeTracker.Entries().Count());
                DisplayTrackedEntities(ctx.ChangeTracker);

                Console.WriteLine("Remove student");
                Console.WriteLine("");
                ctx.Students.Remove(stu1);
                DisplayTrackedEntities(ctx.ChangeTracker);
            }
        }

        private static void DisplayTrackedEntities(DbChangeTracker changeTracker)
        {
            Console.WriteLine("");

            var entries = changeTracker.Entries();
            foreach (var entry in entries)
            {
                Console.WriteLine("Entity name: {0}", entry.Entity.GetType().FullName);
                Console.WriteLine("Status: {0}", entry.State);
            }
            Console.WriteLine("");
            Console.WriteLine("-------------------------------------");
        }

        private static void ConnectedSenario()
        {
            using (var context = new SchoolContext())
            {
                var studentList = context.Students.ToList<Student>();

                //Add student in list
                studentList.Add(new Student() {StudentName = "New Student"});

                //Perform update operation
                Student studentToUpdate = studentList.Where(s => s.StudentName == "Student1").FirstOrDefault<Student>();
                studentToUpdate.StudentName = "Edited student1";

                //Delete student from list
                if (studentList.Count > 0)
                    studentList.Remove(studentList.ElementAt<Student>(0));

                // Todo: SaveChanges will only do update operation not add and delete
                context.SaveChanges();
            }
        }

        /* DbSet.Add() will execute the insert command
         * DbSet.Attach() will attach the entity to the context with Unchanged state
         * DbContext.Entry() used to change/set the entity state
         */
        private static void DisconnectedSenario()
        {
            // disconnected entity graph
            Student disconnectedStudent = new Student() { StudentName = "New Student" };
            disconnectedStudent.StudentAddress = new StudentAddress() { Address1 = "Address", City = "City1" };

            using (var ctx = new SchoolContext())
            {
                //add disconnected Student entity graph to new context instance - ctx
                ctx.Students.Add(disconnectedStudent); 


                // get DbEntityEntry instance to check the EntityState of specified entity
                var studentEntry = ctx.Entry(disconnectedStudent);
                var addressEntry = ctx.Entry(disconnectedStudent.StudentAddress);

                Console.WriteLine("Student EntityState: {0}", studentEntry.State);
                Console.WriteLine("StudentAddress EntityState: {0}", addressEntry.State);
            }
        }

        private static void AddEntity()
        {
            var newStudent = new Student();

            //set student name
            newStudent.StudentName = "Bill";

            //create DBContext object
            using (var dbCtx = new SchoolContext())
            {
                //Add Student object into Students DBset
                dbCtx.Students.Add(newStudent); // or use: dbCtx.Entry(newStudent).State = EntityState.Added;
                
                // call SaveChanges method to save student into database
                dbCtx.SaveChanges();
            }
        }

        private static void UpdateEntity()
        {
            Student stud;
            //1. Get student from DB
            using (var ctx = new SchoolContext())
            {
                stud = ctx.Students.FirstOrDefault(s => s.StudentName == "New Student1");
            }

            //2. change student name in disconnected mode (out of ctx scope)
            if (stud != null)
            {
                stud.StudentName = "Updated Student1";
            }

            //save modified entity using new Context
            using (var dbCtx = new SchoolContext())
            {
                //3. Mark entity as modified
                dbCtx.Entry(stud).State = EntityState.Modified;

                //4. call SaveChanges
                dbCtx.SaveChanges();
            }
        }

        private static void DeleteEntity()
        {
            Student studentToDelete;
            //1. Get student from DB
            using (var ctx = new SchoolContext())
            {
                studentToDelete = ctx.Students.FirstOrDefault(s => s.StudentName == "Student1");
            }

            //Create new context for disconnected scenario
            using (var newContext = new SchoolContext())
            {
                newContext.Entry(studentToDelete).State = EntityState.Deleted;

                newContext.SaveChanges();
            }  
        }

        private static void AddEntityGraph()
        {
            //Create student in disconnected mode
            Student newStudent = new Student() { StudentName = "New Single Student" };

            //Assign new standard to student entity
            newStudent.Standard = new Standard() { StandardName = "New Standard" };

            //add new course with new teacher into student.courses
            newStudent.Courses.Add(new Course() { CourseName = "New Course for single student", Teacher = new Teacher() { TeacherName = "New Teacher" } });

            using (var context = new SchoolContext())
            {
                context.Students.Add(newStudent);
                context.SaveChanges();

                Console.WriteLine("New Student Entity has been added with new StudentId= " + newStudent.StudentId.ToString());
                Console.WriteLine("New Standard Entity has been added with new StandardId= " + newStudent.StandardId.ToString());
                Console.WriteLine("New Course Entity has been added with new CourseId= " + newStudent.Courses.ElementAt(0).CourseId.ToString());
                Console.WriteLine("New Teacher Entity has been added with new TeacherId= " + newStudent.Courses.ElementAt(0).TeacherId.ToString());
            }
        }

        // Complex in this case due to detect state for child entity with Disconnected senario
        private static void UpdateEntityGraph()
        {
            // 1. 
            Standard disconnectedStandard = null;

            using (var context = new SchoolContext())
            {
                context.Configuration.ProxyCreationEnabled = false;

                disconnectedStandard = context.Standards.Where(s => s.StandardId == 58).Include(s => s.Teachers).FirstOrDefault<Standard>();
            }
            //Update Standard in disconnected mode
            disconnectedStandard.StandardName = "Edited Standard Name";

            //Update teachers collection by editing first teacher and adding new teacher
            disconnectedStandard.Teachers.ElementAt(0).TeacherName = "Edited Teacher Name";
            disconnectedStandard.Teachers.Add(new Teacher() { TeacherName = "New Teacher", StandardId = disconnectedStandard.StandardId });

            using (var newContext = new SchoolContext())
            {
                //mark standard based on StandardId
                newContext.Entry(disconnectedStandard).State = disconnectedStandard.StandardId == 0 ? EntityState.Added : EntityState.Modified;

                //mark teacher based on StandardId
                foreach (Teacher tchr in disconnectedStandard.Teachers)
                    newContext.Entry(tchr).State = tchr.TeacherId == 0 ? EntityState.Added : EntityState.Modified;
                
                newContext.SaveChanges();
            }
        }

        private static void ConcurrencyCheck()
        {
            Student student1WithUser1 = null;
            Student student1WithUser2 = null;

            //User 1 gets student
            using (var context = new SchoolContext())
            {
                context.Configuration.ProxyCreationEnabled = false;
                student1WithUser1 = context.Students.Where(s => s.StudentId == 1).Single();
            }
            //User 2 also get the same student
            using (var context = new SchoolContext())
            {
                context.Configuration.ProxyCreationEnabled = false;
                student1WithUser2 = context.Students.Where(s => s.StudentId == 1).Single();
            }
            //User 1 updates Student name
            student1WithUser1.StudentName = "Edited from user1";

            //User 2 updates Student name
            student1WithUser2.StudentName = "Edited from user2";

            /***  Perform  ***/
            //User 1 saves changes first
            using (var context = new SchoolContext())
            {
                try
                {
                    context.Entry(student1WithUser1).State = EntityState.Modified;
                    context.SaveChanges();
                }
                catch (DbUpdateConcurrencyException ex)
                {
                    Console.WriteLine("Optimistic Concurrency exception occured");
                }
            }

            //User 2 saves changes after User 1. 
            //User 2 will get concurrency exection 
            //because CreateOrModifiedDate is different in the database 
            using (var context = new SchoolContext())
            {
                try
                {
                    context.Entry(student1WithUser2).State = EntityState.Modified;
                    context.SaveChanges();
                }
                catch (DbUpdateConcurrencyException ex)
                {
                    Console.WriteLine("Optimistic Concurrency exception occured");
                }
            }

        }

        private static void Table_ValueFunction()
        {
            // is composable which means that it can be used in a LINQ query.
        }

        private static void TestLocalData()
        {
            using (var ctx = new SchoolContext())
            {
                ctx.Students.Add(new Student() { StudentName = "New Student1" });

                // cannot use in delete entity case
                ctx.Students.Remove(ctx.Students.Find(2));
                //ctx.Students.Remove(new Student{StudentId = 270}); // Todo prefer delete entity this way

                // Loop over the unicorns in the context.
                Console.WriteLine("In Local: ");
                foreach (var student in ctx.Students.Local)
                {
                    Console.WriteLine("Found {0}: {1} with state {2}",
                                      student.StudentId, student.StudentName,
                                      ctx.Entry(student).State);
                }

                // Perform a query against the database.
                Console.WriteLine("\nIn DbSet query: ");
                foreach (var student in ctx.Students)
                {
                    Console.WriteLine("Found {0}: {1} with state {2}",
                                      student.StudentId, student.StudentName,
                                      ctx.Entry(student).State);
                }
            }
        }

        private static void EagerLoading()
        {
            using (var ctx = new SchoolContext())
            {
                var stud = ctx.Students.Include(x => x.Standard).FirstOrDefault(x => x.StudentId == 2);
                var stud1 = ctx.Students.Include(x => x.Standard.Teachers).FirstOrDefault(x => x.StudentId == 2);
            }
        }

        private static void LazyLoading()
        {
            /* Lazy load: ProxyCreationEnabled, LazyLoadingEnabled, virtual */

            using (var ctx = new SchoolContext())
            {
                var stud1 = ctx.Students.FirstOrDefault(x => x.StudentId == 2);

                var addr = stud1.StudentAddress;
            }
        }

        private static void ExplicitLoading()
        {
            // Lazy loading in case lazy loading disabled

            using (var context = new SchoolContext())
            {
                //Disable Lazy loading
                context.Configuration.LazyLoadingEnabled = false;

                var student = (from s in context.Students
                               where s.StudentId == 12 // this student has a Standard
                               select s).FirstOrDefault<Student>();

                context.Entry(student).Reference(s => s.Standard).Load(); // for one entity
                context.Entry(student).Collection(s => s.Courses).Load(); // for a collection
            }     
        }

        private static void TPH()
        {
            using (var context = new SchoolContext())
            {
                context.Users.Add(new FeaturedUser
                                  {
                                      FirstName = "Dukhabandhu",
                                      LastName = "Sahoo",
                                      IsFeatured = true,
                                      SubscriptionDate = DateTime.Now
                                  });

                //context.SaveChanges();
            }
        }

        private static void TPT()
        {
            using (var context = new SchoolContext())
            {
                context.UserTpts.Add(new FearturedUserTPT
                {
                    FirstName = "Dukhabandhu",
                    LastName = "Sahoo",
                    IsFeatured = true,
                    SubscriptionDate = DateTime.Now
                });

                //context.SaveChanges();
            }

            using (var context = new SchoolContext())
            {
                var firstUser = context.UserTpts.FirstOrDefault();
                if (firstUser != null)
                {
                    context.UserTpts.Remove(firstUser); // will also remove related FeaturedUserTpts's record
                    context.SaveChanges();
                }
            }
        }

        private static void NPlus1Problem()
        {
            using (var context = new SchoolContext())
            {
                // Lazy loads all N Orders in single select
                foreach (var standard in context.Standards)
                {
                    // Lazy loads all Items for single order => executed N times
                    foreach (var student in standard.Students)
                    {
                        var courses = student.Courses;
                    }
                }
            }

            //This is easily solved by:
            using (var context = new SchoolContext())
            {
                // Eager load all Orders and their items in single query
                foreach (var sta in context.Standards.Include(x => x.Students))
                {
                    foreach (var stud in sta.Students)
                    {
                        var courses = stud.Courses;
                    }
                }
            }
        }
    }
}
